package game.gamestorage.texas.db;

import game.Action;

import java.util.ArrayList;

import util.DebugOut;

/**
 * This class holds the sql-queries for writing to db</br>
 * 
 * @author Witthold/Korol
 */
public final class SetterQuery {

	/*
	 * TODO possible improvement by using "INSERT ... ON DUPLICATE KEY UPDATE" // http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
	 * @formatter:off to improve human readability // since Eclipse 3.6
	 */

	/**
	 * @param limited - limited:1; pot-limit:0; no-limit: -1
	 * @param minBet - only int possible
	 * @param maxBet - only int possible
	 * @param num_players -
	 * @param style - cashgame:1; tournament:2; doyle:3;
	 * @param buyin - only int possible
	 * 
	 * @return String sql-query
	 */
	protected static String insertGameDef(int limited, int minBet, int maxBet, int num_players, int style, int buyin) {

		// @formatter:off
		String sql = "INSERT INTO gamedef " + "(limited, minBet, maxBet, num_players, style, buyin) VALUES " + "(" + limited + ", " + minBet + ", " + maxBet
				+ ", " + num_players + ", " + style + ", " + buyin + ");";
		// @formatter:on
		return sql;
	}


	/**
	 * @param limited - limited:1; pot-limit:0; no-limit: -1
	 * @param minBet - only int possible
	 * @param maxBet - only int possible
	 * @param num_players -
	 * @param style - cashgame:1; tournament:2; doyle:3;
	 * @param buyin - only int possible
	 * 
	 * @return String sql-query
	 */
	protected static String selectGameDefID(int limited, int minBet, int maxBet, int num_players, int style, int buyin) {

		// @formatter:off
		String sql = "SELECT id FROM gamedef WHERE " + "limited = " + limited + " AND " + "minBet = " + minBet + " AND " + "maxBet = " + maxBet + " AND "
				+ "num_players = " + num_players + " AND " + "style = " + style + " AND " + "buyin = " + buyin + ";";
		// @formatter:on
		return sql;
	}


	/**
	 * @param gamedef_id - already generated by db
	 * @return String sql-query
	 */
	protected static String insertCompetition(int gamedef_id) {

		// @formatter:off
		String sql = "INSERT INTO competition " + "(gamedef_id) VALUES " + "(" + gamedef_id + ");";
		// @formatter:on
		return sql;
	}


	/**
	 * @param alias - all playernames
	 * @return String sql-query
	 */
	protected static String selectPlayerAliasesIDs(String[] alias) {

		// @formatter:on
		String sql = "SELECT alias, id FROM player WHERE ";
		StringBuffer sb = new StringBuffer(sql);

		for (int i = 0; i < alias.length; i++) {
			sb.append("alias = '" + alias[i] + "'");
			if (i < alias.length - 1) {
				sb.append(" OR ");
			} else {
				sb.append(";");
			}
		}

		sql = sb.toString();
		DebugOut.showVerboseSqlQuery("String sql: " + sql);

		// @formatter:on
		return sql;
	}


	/**
	 * @param alias - name of specific player
	 * @return String sql-query
	 */
	protected static String insertPlayer(String alias) {

		// @formatter:off
		String sql = "INSERT INTO player " + "(alias) VALUES " + "('" + alias + "');";
		// @formatter:on
		return sql;
	}


	/**
	 * @param player_ids already generated by db
	 * @param cash_init - with how much credits do the players start this? -
	 * @param competition_id already generated by db
	 * @return String sql-query
	 */
	protected static String insertCompetitionPlayerMap(int[] player_ids, int[] cash_init, int competition_id) {

		// @formatter:off
		String sql = "INSERT INTO competition_player_map (cash_init, player_id, competition_id) VALUES ";
		StringBuffer sb = new StringBuffer(sql);

		for (int i = 0; i < player_ids.length; i++) {
			sb.append("(" + cash_init[i] + ", " + player_ids[i] + ", " + competition_id + ")");
			if (i < player_ids.length - 1) {
				sb.append(", ");
			} else {
				sb.append(";");
			}
		}

		sql = sb.toString();
		DebugOut.showVerboseSqlQuery("String sql: " + sql);

		// @formatter:on
		return sql;
	}


	/**
	 * @param sb - smallblind
	 * @param bb - bigblind
	 * @param ante - forced bet for all players
	 * @param competition_id already generated by db
	 * @return String sql-query
	 */
	protected static String insertHand(int sb, int bb, int ante, int competition_id) {

		// @formatter:off
		String sql = "INSERT INTO hand " + "(sb, bb, ante, competition_id) VALUES " + "(" + sb + ", " + bb + ", " + ante + ", " + competition_id + ");";
		// @formatter:on
		return sql;
	}


	/**
	 * @param position
	 * 			Position of the player in this hand
	 * 			(0: first player behind the button; 1: second player behind the button; ...)
	 * @param cash_init - with how much credits do the players start this? -
	 * @param player_id already generated by db
	 * @param hand_id already generated by db
	 * @return String sql-query
	 */
	protected static String insertHandPlayerMap(int position, int cash_init, int player_id, int hand_id) {

		// @formatter:off
		String sql = "INSERT INTO hand_player_map " + "(position, cash_init, player_id, hand_id) VALUES " + "(" + position + ", " + cash_init + ", "
				+ player_id + ", " + hand_id + ");";

		DebugOut.showVerboseSqlQuery("String sql: " + sql);
		// @formatter:on
		return sql;
	}


	/**
	 * @param value of the card (combination of number and suite)
	 * @param hand_player_map_id already generated by db
	 * @return String sql-query
	 */
	protected static String insertCardOfPlayer(int value, int hand_player_map_id) {

		// @formatter:off
		String sql = "INSERT INTO card_of_player " + "(hand_player_map_id, value) VALUES " + "(" + hand_player_map_id + ", " + value + ");";

		DebugOut.showVerboseSqlQuery("String sql: " + sql);
		// @formatter:on
		return sql;
	}


	/**
	 * @param hand_id already generated by db
	 * @param timestamp as unix timestamp
	 * @return String sql-query
	 */
	protected static String insertRound(int hand_id, long timestamp) {

		// @formatter:off
		String sql = "INSERT INTO round " + "(hand_id, timestamp) VALUES " + "(" + hand_id + ", " + timestamp + ");";
		// @formatter:on
		return sql;
	}


	/**
	 * @param value of the card (combination of number and suite)
	 * @param round_id already generated by db
	 * @return String sql-query
	 */
	protected static String insertCommonCards(int[] value, int round_id) {

		// @formatter:off
		String sql = "INSERT INTO card_common (value, round_id) VALUES ";
		StringBuffer sb = new StringBuffer(sql);

		for (int i = 0; i < value.length; i++) {
			sb.append("(" + value[i] + ", " + round_id + ")");
			if (i < value.length - 1) {
				sb.append(", ");
			} else {
				sb.append(";");
			}
		}
		sql = sb.toString();
		DebugOut.showVerboseSqlQuery("String sql: " + sql);
		// @formatter:on
		return sql;
	}


	/**
	 * @param bets - ArrayList of Actions of the players in this round
	 * @param round_id already generated by db
	 * @param player_ids already generated by db
	 * @return String sql-query
	 */
	protected static String insertBets(ArrayList<Action> bets, int round_id, int[] player_ids) {

		// @formatter:off
		String sql = "INSERT INTO bet (action, betsize, " + "potsize, timestamp, " + "round_id, player_id) VALUES ";

		StringBuffer sb = new StringBuffer(sql);

		for (int i = 0; i < bets.size(); i++) {

			int betInt = bets.get(i).toInt();

			if (betInt != -11) {
				sb.append("(" + betInt + ", " + bets.get(i).getChange() + ", " + bets.get(i).getPotBefore() + ", " + bets.get(i).getTimestamp() + ", "
						+ round_id + ", " + player_ids[bets.get(i).getPlayer()] + "),");
			}
		}
		sb.setCharAt(sb.length() - 1, ';');

		sql = sb.toString();

		DebugOut.showVerboseSqlQuery("String sql: " + sql);
		// @formatter:on
		return sql;
	}


	/**
	 * @param finalpot - total at the end of the hand
	 * @param hand_id already generated by db
	 * @return String sql-query
	 */
	protected static String updateFinalPot(int finalpot, int hand_id) {

		// @formatter:off
		String sql = "UPDATE hand SET" + " finalpot = " + finalpot + " WHERE " + "id = " + hand_id + ";";
		// @formatter:on
		return sql;
	}


	/**
	 * @param cash_delta - diff between before and after played hand
	 * @param player_id already generated by db
	 * @param hand_id already generated by db
	 * @return String sql-query
	 */
	protected static String updateCashDelta(int cash_delta, int player_id, int hand_id) {

		// @formatter:off
		String sql = "UPDATE hand_player_map SET" + " cash_delta = " + cash_delta + " WHERE " + "player_id = " + player_id + " AND " + "hand_id = " + hand_id
				+ ";";
		// @formatter:on
		return sql;
	}


	/**
	 * @param rank - rank of this player
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @return String sql-query
	 */
	protected static String updateRank(int rank, int player_id, int competition_id) {

		// @formatter:off
		String sql = "UPDATE competition_player_map SET" + " rank = " + rank + " WHERE " + "player_id = " + player_id + " AND " + "competition_id = "
				+ competition_id + ";";
		// @formatter:on
		return sql;
	}


	/*
	 * 
	 * 
	 * 
	 * 
	 * 
	 */

	/**
	 * For TESTING only!
	 * 
	 * @param tblName -
	 * @return String sql-query
	 */
	protected static String emptyTable(String tblName) {

		// @formatter:off
		String sql = "TRUNCATE TABLE " + tblName + ";";
		// @formatter:on
		return sql;
	}

}